﻿using System.Collections.Generic;
using System.Data;
using IceCS.GoodsSale.Models.GoodsModels;

namespace IceCS.GoodsSale.DAL
{
    public class SaleAccesser
    {
        DBHelper dh = new DBHelper();

        //多表连接查询商品销售信息的方法，返回一个销售信息列表
        public List<Sale> SelectSales()
        {
            string sqlStr = "select TB_Sale.SaleID,TB_Sale.GoodsID,TB_Goods.Name,TB_Goods.InPrice,TB_Goods.OutPrice,TB_Sale.Quantity,TB_Sale.TotalPrice,TB_Sale.Earnings,TB_Sale.Date,TB_Sale.Seller from TB_Sale inner join TB_Goods on TB_Sale.GoodsID = TB_Goods.GoodsID";
            DataTable dt = dh.GetDataTable(sqlStr);
            List<Sale> list = new List<Sale>();
            foreach (DataRow r in dt.Rows)
            {
                Sale sale = new Sale();
                sale.SaleID = int.Parse(r["SaleID"].ToString());
                sale.GoodsID = r["GoodsID"].ToString();
                sale.Name = r["Name"].ToString();
                sale.InPrice = decimal.Parse(r["InPrice"].ToString());
                sale.OutPrice = decimal.Parse(r["OutPrice"].ToString());
                sale.Quantity = int.Parse(r["Quantity"].ToString());
                sale.TotalPrice = decimal.Parse(r["TotalPrice"].ToString());
                sale.Earnings = decimal.Parse(r["Earnings"].ToString());
                sale.Date = r["Date"].ToString();
                sale.Seller = r["Seller"].ToString();
                list.Add(sale);
            }
            return list;
        }

        //按条件多表连接查询商品销售信息的方法，返回一个销售信息列表
        public List<Sale> SelectSales(string selectCondition, string selectValue)
        {
            string sqlStr = "select TB_Sale.SaleID,TB_Sale.GoodsID,TB_Goods.Name,TB_Goods.InPrice,TB_Goods.OutPrice,TB_Sale.Quantity,TB_Sale.TotalPrice,TB_Sale.Earnings,TB_Sale.Date,TB_Sale.Seller from TB_Sale inner join TB_Goods on TB_Sale.GoodsID = TB_Goods.GoodsID where TB_Sale." + selectCondition + "='" + selectValue + "'";
            DataTable dt = dh.GetDataTable(sqlStr);
            List<Sale> list = new List<Sale>();
            foreach (DataRow r in dt.Rows)
            {
                Sale sale = new Sale();
                sale.SaleID = int.Parse(r["SaleID"].ToString());
                sale.GoodsID = r["GoodsID"].ToString();
                sale.Name = r["Name"].ToString();
                sale.InPrice = decimal.Parse(r["InPrice"].ToString());
                sale.OutPrice = decimal.Parse(r["OutPrice"].ToString());
                sale.Quantity = int.Parse(r["Quantity"].ToString());
                sale.TotalPrice = decimal.Parse(r["TotalPrice"].ToString());
                sale.Earnings = decimal.Parse(r["Earnings"].ToString());
                sale.Date = r["Date"].ToString();
                sale.Seller = r["Seller"].ToString();
                list.Add(sale);
            }
            return list;
        }

        //按销售员多表连接查询商品销售信息的方法，返回一个销售信息列表
        public List<Sale> SelectSellerSales(string seller)
        {
            string sqlStr = "select TB_Sale.SaleID,TB_Sale.GoodsID,TB_Goods.Name,TB_Goods.InPrice,TB_Goods.OutPrice,TB_Sale.Quantity,TB_Sale.TotalPrice,TB_Sale.Earnings,TB_Sale.Date,TB_Sale.Seller from TB_Sale inner join TB_Goods on TB_Sale.GoodsID = TB_Goods.GoodsID where TB_Sale.Seller='" + seller + "'";
            DataTable dt = dh.GetDataTable(sqlStr);
            List<Sale> list = new List<Sale>();
            foreach (DataRow r in dt.Rows)
            {
                Sale sale = new Sale();
                sale.SaleID = int.Parse(r["SaleID"].ToString());
                sale.GoodsID = r["GoodsID"].ToString();
                sale.Name = r["Name"].ToString();
                sale.InPrice = decimal.Parse(r["InPrice"].ToString());
                sale.OutPrice = decimal.Parse(r["OutPrice"].ToString());
                sale.Quantity = int.Parse(r["Quantity"].ToString());
                sale.TotalPrice = decimal.Parse(r["TotalPrice"].ToString());
                sale.Earnings = decimal.Parse(r["Earnings"].ToString());
                sale.Date = r["Date"].ToString();
                sale.Seller = r["Seller"].ToString();
                list.Add(sale);
            }
            return list;
        }

        //按销售日期多表连接查询商品销售信息的方法，返回一个销售信息列表
        public List<Sale> SelectDateSales(string date)
        {
            string sqlStr = "select TB_Sale.SaleID,TB_Sale.GoodsID,TB_Goods.Name,TB_Goods.InPrice,TB_Goods.OutPrice,TB_Sale.Quantity,TB_Sale.TotalPrice,TB_Sale.Earnings,TB_Sale.Date,TB_Sale.Seller from TB_Sale inner join TB_Goods on TB_Sale.GoodsID = TB_Goods.GoodsID where TB_Sale.Date like '" + date + "%'";
            DataTable dt = dh.GetDataTable(sqlStr);
            List<Sale> list = new List<Sale>();
            foreach (DataRow r in dt.Rows)
            {
                Sale sale = new Sale();
                sale.SaleID = int.Parse(r["SaleID"].ToString());
                sale.GoodsID = r["GoodsID"].ToString();
                sale.Name = r["Name"].ToString();
                sale.InPrice = decimal.Parse(r["InPrice"].ToString());
                sale.OutPrice = decimal.Parse(r["OutPrice"].ToString());
                sale.Quantity = int.Parse(r["Quantity"].ToString());
                sale.TotalPrice = decimal.Parse(r["TotalPrice"].ToString());
                sale.Earnings = decimal.Parse(r["Earnings"].ToString());
                sale.Date = r["Date"].ToString();
                sale.Seller = r["Seller"].ToString();
                list.Add(sale);
            }
            return list;
        }

        //按销售员和销售日期多表连接查询商品销售信息的方法，返回一个销售信息列表
        public List<Sale> SelectSellerAndDateSales(string seller, string date)
        {
            string sqlStr = "select TB_Sale.SaleID,TB_Sale.GoodsID,TB_Goods.Name,TB_Goods.InPrice,TB_Goods.OutPrice,TB_Sale.Quantity,TB_Sale.TotalPrice,TB_Sale.Earnings,TB_Sale.Date,TB_Sale.Seller from TB_Sale inner join TB_Goods on TB_Sale.GoodsID = TB_Goods.GoodsID where TB_Sale.Seller='" + seller + "' and TB_Sale.Date like '" + date + "%'";
            DataTable dt = dh.GetDataTable(sqlStr);
            List<Sale> list = new List<Sale>();
            foreach (DataRow r in dt.Rows)
            {
                Sale sale = new Sale();
                sale.SaleID = int.Parse(r["SaleID"].ToString());
                sale.GoodsID = r["GoodsID"].ToString();
                sale.Name = r["Name"].ToString();
                sale.InPrice = decimal.Parse(r["InPrice"].ToString());
                sale.OutPrice = decimal.Parse(r["OutPrice"].ToString());
                sale.Quantity = int.Parse(r["Quantity"].ToString());
                sale.TotalPrice = decimal.Parse(r["TotalPrice"].ToString());
                sale.Earnings = decimal.Parse(r["Earnings"].ToString());
                sale.Date = r["Date"].ToString();
                sale.Seller = r["Seller"].ToString();
                list.Add(sale);
            }
            return list;
        }
    }
}
